/***********************************************************************************************************************************
 *    Name: Login_Owners.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: Create a least-privilege account for database and job owner.
**/
USE master;
DECLARE @tsql_command NVARCHAR(MAX);
SET @tsql_command= N'BEGIN TRY; CREATE LOGIN [DBOwner] WITH PASSWORD= ''' + CAST(NEWID() AS NVARCHAR(36)) + N''', SID= 0x5A595369A5A9F14DBC5D5FEE51F6CEA0; END TRY BEGIN CATCH END CATCH;';
EXECUTE (@tsql_command);
ALTER LOGIN DBOwner DISABLE;
DENY CONNECT SQL TO DBOwner;
SET @tsql_command= N'';
SELECT @tsql_command= @tsql_command + N' ALTER AUTHORIZATION ON DATABASE::[' + d.name + '] TO DBOwner;'
  FROM sys.databases d LEFT JOIN master.sys.server_principals l ON (d.owner_sid = l.sid)
  WHERE d.name IN (SELECT database_name FROM master.DBAdmin.DBList(N'@'))
	AND ( (l.name IN (N'sa', USER_NAME()) OR l.name IS Null) );

PRINT @tsql_command;
EXECUTE (@tsql_command);
GO

USE master;
DECLARE @tsql_command NVARCHAR(MAX);
SET @tsql_command= N'BEGIN TRY; CREATE LOGIN [JobOwner] WITH PASSWORD= ''' + CAST(NEWID() AS NVARCHAR(36)) + N''', SID= 0x7b06a7509c0f0042b03da5d826eb90d9; END TRY BEGIN CATCH END CATCH;';
EXECUTE (@tsql_command);
ALTER LOGIN JobOwner DISABLE;
CREATE USER JobOwner FROM LOGIN JobOwner WITH DEFAULT_SCHEMA= DBAdmin;
EXECUTE dbo.sp_addrolemember @rolename = N'DBAdmin', @membername = 'JobOwner';

